package com.zlx.dao;

import com.zlx.entities.User;
import com.zlx.utils.Page;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;

import java.util.List;

/**
 * @Author 周罗校
 * @Description 用户相关的数据查询
 * @ClassName UserDao
 * @date 2020.09.23 11:18:57
 * 该版权归周罗校所有，最终解释权以官方为准
 */
public class UserDao extends BaseDao{
    /**
     * @Author 周罗校
     * @Description 检查登陆
     * @Param [username, password]
     * @return com.zlx.entities.User
     * @Date 2020.09.23 11:22:26
     **/
    public User checkLogin(String username, String password){
        String sql = "select * from user where username = ? and password = ?";
        try {
            User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), username, password);
            return user;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }
    /**
     * @Author 周罗校
     * @Description 查询记录数
     * @Param [name]
     * @return java.lang.Integer
     * @Date 2020.09.23 15:27:44
     **/
    public Integer getUserCount(String name){
        String sql = "select count(*) as count from user where username like ?";
        return jdbcTemplate.queryForObject(sql, Integer.class, "%"+name+"%");
    }
    /**
     * @Author 周罗校
     * @Description 用户列表分页查询
     * @Param [page, name]
     * @return java.util.List<com.zlx.entities.User>
     * @Date 2020.09.23 17:40:32
     **/
    public List<User> getUserList(Page<User> page,String name){
        String sql = "SELECT\n" +
                "a.id id,\n" +
                "a.username username,\n" +
                "a.email email,\n" +
                "a.real_name real_name,\n" +
                "a.age age,\n" +
                "a.phone phone,\n" +
                "a.gender gender,\n" +
                "a.login_time login_time,\n" +
                "DATE_FORMAT(a.register_time,'%Y年%m月%d日 %H时%i分%s秒') register_time,\n" +
                "b.NAME deptName \n" +
                "FROM\n" +
                "USER a\n" +
                "LEFT JOIN dept b ON a.dept_id = b.id\n" +
                "WHERE username LIKE ? LIMIT ?,?";
        /*System.out.println(page.getStart());
        System.out.println(page.getPageSize());*/
        try {
            List<User> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class),
                    "%"+name+"%",page.getStart(),page.getPageSize());
            return list;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }
    /**
     * @Author 周罗校
     * @Description 根据id删除用户
     * @Param [id]
     * @return void
     * @Date 2020.09.23 17:42:55
     **/
    public void deleteUserById(Integer id){
        String sql = "delete from user where id = ?";
        int update = jdbcTemplate.update(sql, id);
    }
    /**
     * @Author 周罗校
     * @Description 通过id查询用户
     * @Param [id]
     * @return com.zlx.entities.User
     * @Date 2020.09.23 19:32:30
     **/
    public User getUserById(Integer id){
        String sql = "SELECT\n" +
                "a.id id,\n" +
                "a.username username,\n" +
                "a.email email,\n" +
                "a.real_name real_name,\n" +
                "a.age age,\n" +
                "a.phone phone,\n" +
                "a.gender gender,\n" +
                "a.login_time login_time,\n" +
                "a.register_time register_time,\n" +
                "a.dept_id dept_id,\n" +
                "b.NAME deptName \n" +
                "FROM\n" +
                "USER a\n" +
                "LEFT JOIN dept b ON a.dept_id = b.id\n" +
                "WHERE a.id = ?";
        User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
        return user;
    }
    public void update(User user){
        String sql = "update user set username = ?,real_name = ?,age = ?,dept_id = ?,phone = ?,email = ?,gender = ? where id =?";
        jdbcTemplate.update(sql,
                user.getUsername(),user.getRealName(),user.getAge(),user.getDeptId(),
                user.getPhone(),user.getEmail(),user.getGender(), user.getId()
                );
    }
    /**
     * @Author 周罗校
     * @Description 根据id修改头像路径信息
     * @Param [pic, id]
     * @return void
     * @Date 2020.09.24 20:07:30
     **/
    public void updatePicById(String pic,Integer id){
        String sql = "update user set pic=? where id =?";
        jdbcTemplate.update(sql, pic, id);
    }
    /**
     * @Author 周罗校
     * @Description 根据部门id查找User
     * @Param [deptId]
     * @return java.util.List<com.zlx.entities.User>
     * @Date 2020.09.25 16:18:00
     **/
    public List<User> getUserByDeptId(Integer deptId){
        String sql = "select * from user where dept_id = ?";
        List<User> query = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class), deptId);
        return query;
    }
    public void updatePasswordByUsername(User user){
        String sql = "update user set password = ? where username =?";
        jdbcTemplate.update(sql,user.getPassword(),user.getUsername());
    }
    /**
     * @Author 周罗校
     * @Description 根据wxOpenid查询用户是否存在
     * @Param [wxOpenid]
     * @return com.zlx.entities.User
     * @Date 2020.09.28 09:33:14
     **/
    public User queryUserByUserName(String wxOpenid){
        String sql = "select * from user where wx_openid =?";
        try {
            User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), wxOpenid);
            return user;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }
    /**
     * @Author 周罗校
     * @Description 插入微信用户
     * @Param [user]
     * @return void
     * @Date 2020.09.28 09:32:06
     **/
    public void insertWeChatUser(User user){
        String sql = "insert user(id,wx_openid,gender,username,pic) values(null,?,?,?,?)";
        jdbcTemplate.update(sql,user.getWxOpenid(),user.getGender(),user.getUsername(),user.getPic());
    }
    public User queryUserByQxOpenid(String qqOpenid){
        String sql = "select * from user where qq_openid =?";
        try {
            User user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), qqOpenid);
            return user;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }
    public void insertQQUser(User user){
        String sql = "insert user(id,qq_openid,gender,username,real_name,pic) values(null,?,?,?,?,?)";
        jdbcTemplate.update(sql,user.getQqOpenid(),user.getGender(),user.getUsername(),user.getRealName(),user.getPic());
    }
}
